﻿/*
Deployment script for RSLogAndParamCacheDB
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "RSLogAndParamCacheDB"
:setvar DefaultDataPath "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\"

GO
USE [master]

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [DefaultReportParamStore], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB)
    LOG ON (NAME = [DefaultReportParamStore_log], FILENAME = '$(DefaultDataPath)$(DatabaseName)_log.ldf', MAXSIZE = 2097152 MB, FILEGROWTH = 10 %)
GO
EXECUTE sp_dbcmptlevel [$(DatabaseName)], 100;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings for DB_CHAINING or TRUSTWORTHY cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET HONOR_BROKER_PRIORITY OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)]

GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO

GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.	
 Use SQLCMD syntax to include a file in the pre-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO

GO
PRINT N'Creating dbo.Configuration...';


GO
CREATE TABLE [dbo].[Configuration] (
    [ConfigId]    INT             IDENTITY (1, 1) NOT NULL,
    [ConfigType]  NVARCHAR (255)  NULL,
    [ConfigValue] NVARCHAR (255)  NULL,
    [Description] NVARCHAR (1000) NULL
);


GO
PRINT N'Creating dbo.DefaultReportParameterExecution...';


GO
CREATE TABLE [dbo].[DefaultReportParameterExecution] (
    [ExecutionId]     INT            IDENTITY (1, 1) NOT NULL,
    [UserId]          NVARCHAR (255) NULL,
    [ReportServerUrl] NVARCHAR (128) NULL,
    [ReportName]      NVARCHAR (128) NULL,
    [ExecutionTime]   DATETIME       NULL
);


GO
PRINT N'Creating dbo.DefaultReportParameters...';


GO
CREATE TABLE [dbo].[DefaultReportParameters] (
    [ExecutionId]    INT            NULL,
    [ParameterName]  NVARCHAR (128) NULL,
    [ParameterValue] NVARCHAR (MAX) NULL
);


GO
PRINT N'Creating dbo.DefaultReportParameterExecution.IX_DefaultReportParameterExecution...';


GO
CREATE NONCLUSTERED INDEX [IX_DefaultReportParameterExecution]
    ON [dbo].[DefaultReportParameterExecution]([UserId] ASC, [ReportName] ASC, [ReportServerUrl] ASC, [ExecutionTime] DESC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);


GO
PRINT N'Creating dbo.DefaultReportParameters.IX_DefaultReportParameters...';


GO
CREATE NONCLUSTERED INDEX [IX_DefaultReportParameters]
    ON [dbo].[DefaultReportParameters]([ExecutionId] DESC, [ParameterName] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0);


GO
PRINT N'Creating On column: ExecutionTime...';


GO
ALTER TABLE [dbo].[DefaultReportParameterExecution]
    ADD DEFAULT (getdate()) FOR [ExecutionTime];


GO
PRINT N'Creating dbo.UQ_Configuration_ConfigType...';


GO
ALTER TABLE [dbo].[Configuration]
    ADD CONSTRAINT [UQ_Configuration_ConfigType] UNIQUE NONCLUSTERED ([ConfigType] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_Configuration...';


GO
ALTER TABLE [dbo].[Configuration]
    ADD CONSTRAINT [PK_Configuration] PRIMARY KEY CLUSTERED ([ConfigId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.PK_DefaultReportParameterExecution...';


GO
ALTER TABLE [dbo].[DefaultReportParameterExecution]
    ADD CONSTRAINT [PK_DefaultReportParameterExecution] PRIMARY KEY CLUSTERED ([ExecutionId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.FK_DefaultReportParameters_DefaultReportParameterExecution...';


GO
ALTER TABLE [dbo].[DefaultReportParameters]
    ADD CONSTRAINT [FK_DefaultReportParameters_DefaultReportParameterExecution] FOREIGN KEY ([ExecutionId]) REFERENCES [dbo].[DefaultReportParameterExecution] ([ExecutionId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
PRINT N'Creating dbo.Split...';


GO
CREATE FUNCTION [dbo].[Split]
(@String VARCHAR (MAX), @Delimiter VARCHAR (5))
RETURNS 
    @SplittedValues TABLE (
        [OccurenceId] SMALLINT      IDENTITY (1, 1) NOT NULL,
        [SplitValue]  VARCHAR (MAX) NULL)
AS
BEGIN
DECLARE @SplitLength INT


WHILE LEN(@String) > 0
BEGIN
SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN
0 THEN
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)


INSERT INTO @SplittedValues
SELECT SUBSTRING(@String,1,@SplitLength)


SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0
THEN ''
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
END
RETURN
END


GO
PRINT N'Creating dbo.SpGetDefaultParameterForUser...';


GO
CREATE PROCEDURE [dbo].[SpGetDefaultParameterForUser]
@UserId VARCHAR (50), @ReportServerUrl VARCHAR (128), @ReportName VARCHAR (128), @ParameterName VARCHAR (128), @DataType [sysname]
AS
IF (SELECT TOP 1 ConfigValue FROM dbo.Configuration WHERE ConfigType = 'HashUserId') = 1
	SET @UserId = HashBytes('SHA1', @UserId)
	
DECLARE @ParameterValue VARCHAR(MAX)
SET @ParameterValue = ''

DECLARE @ROWCOUNT INT

DECLARE @LatestExecutionId INT

--Start :>>>>>>thankful update from Codeplex -- User: "blase"
		--avoids reports from initial failing when user runs report the FIRST time
		--Check if the user already has default parameters, if not return NULL
		SELECT TOP(1) @LatestExecutionId = ExecutionId
		FROM        dbo.DefaultReportParameterExecution Pe
		WHERE        Pe.UserId = @UserId 
		ORDER BY    ExecutionTime DESC

		IF (@LatestExecutionId IS NULL) 
			BEGIN
			SELECT NULL AS ParameterValue
			RETURN
			END
		-- end Check
--End :<<<<<< Update from Codeplex 

--Get the latest execution of the report for the particular user
SELECT TOP(1) @LatestExecutionId = ExecutionId
FROM dbo.DefaultReportParameterExecution Pe
WHERE
	Pe.UserId = @UserId 
	AND Pe.ReportName = @ReportName
	AND Pe.ReportServerUrl = @ReportServerUrl
ORDER BY ExecutionTime DESC

--Check first if the information should be tracked at all
IF NOT (SELECT TOP 1 ConfigValue FROM dbo.Configuration WHERE ConfigType = 'TrackParameterValues') = 1
	RETURN 
	
--Get the values the user selected and transform them to be eventually
--multi values to be selected
SELECT @ParameterValue = @ParameterValue + ParameterValue + ';'
FROM dbo.DefaultReportParameters P
WHERE
	P.ParameterName = @ParameterName
	AND P.ExecutionId = @LatestExecutionId

SET @ROWCOUNT = @@ROWCOUNT

PRINT @LatestExecutionId
PRINT @ROWCOUNT 
PRINT LEN(@ParameterValue)
PRINT CASE WHEN @ParameterValue IS NULL THEN 'NULL' ELSE @ParameterValue END

IF @ROWCOUNT = 1 AND @ParameterValue IS NULL
BEGIN
	SELECT NULL AS ParameterValue
	RETURN
END

--Return the values in a typed format
IF @DataType = 'Boolean'
	SELECT CAST(SplitValue AS BIT) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

IF @DataType = 'DateTime'
	SELECT CAST(SplitValue AS DATETIME) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

IF @DataType = 'Text'
	SELECT CAST(SplitValue AS VARCHAR(MAX)) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')
	
IF @DataType = 'Integer'
	SELECT CAST(SplitValue AS INT) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

IF @DataType = 'Float'
	SELECT CAST(SplitValue AS FLOAT) AS ParameterValue
	FROM dbo.Split(@ParameterValue, ';')

RETURN



GO
PRINT N'Creating dbo.SpStoreDefaultParameterForUser...';


GO
CREATE PROCEDURE [dbo].[SpStoreDefaultParameterForUser]
@ParameterValues VARCHAR (MAX), @UserId VARCHAR (50), @ReportServerUrl VARCHAR (128), @ReportName VARCHAR (128)
AS
DECLARE @RowCount INT
DECLARE @ExecutionId INT
DECLARE @ParameterStack VARCHAR(MAX)
DECLARE @ParameterName VARCHAR(MAX)


IF (SELECT TOP(1) ConfigValue FROM dbo.Configuration WHERE ConfigType = 'HashUserId') = 1
	SET @UserId = HashBytes('SHA1', @UserId)
	
INSERT INTO dbo.DefaultReportParameterExecution
(
	UserId,
	ReportServerUrl,
	ReportName
)
SELECT 
	@UserId,
	@ReportServerUrl,
	@ReportName
	
SET @ExecutionId = SCOPE_IDENTITY()
	
SELECT OccurenceId, SplitValue
INTO #Parameters
FROM dbo.Split(@ParameterValues,';' )
s
SET @RowCount = @@ROWCOUNT

WHILE @RowCount > 0
BEGIN

	SELECT @ParameterStack = SplitValue FROM #Parameters WHERE OccurenceId = @RowCount
		
	SELECT SplitValue FROM #Parameters WHERE OccurenceId = @RowCount
	
	SELECT @ParameterName = SplitValue FROM dbo.Split(@ParameterStack, '^') Where OccurenceId = 1
	SELECT @ParameterValues = CASE WHEN SplitValue = '<NULL>' THEN NULL ELSE SplitValue END FROM dbo.Split(@ParameterStack, '^') Where OccurenceId = 2

PRINT @ParameterName + @ParameterValues

	IF @ParameterValues IS NULL
		INSERT INTO dbo.DefaultReportParameters
		(
			ExecutionId,
			ParameterName,
			ParameterValue
		)
		Select 
			@ExecutionId,
			@ParameterName,
			NULL
		
	ELSE
	INSERT INTO dbo.DefaultReportParameters
	(
		ExecutionId,
		ParameterName,
		ParameterValue
	)
	Select 
		@ExecutionId,
		@ParameterName,
		SplitValue
	FROM 
	dbo.Split(@ParameterValues, '|')
	
	SET @RowCount = @RowCount -1 

END


GO

GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

IF NOT EXISTS (SELECT * FROM dbo.Configuration WHERE ConfigType = 'HashUserId') 
INSERT INTO dbo.Configuration
(
	ConfigType,
	ConfigValue,
	Description
)
SELECT
	'HashUserId',
	1,
	'Determines if the UserId should be hased for security reasons'



IF NOT EXISTS (SELECT * FROM dbo.Configuration WHERE ConfigType = 'TrackParameterValues')
INSERT INTO dbo.Configuration
(
	ConfigType,
	ConfigValue,
	Description
)
SELECT
	'TrackParameterValues',
	1,
	'Determines if the parameters should be tracked at all'



GO

GO

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        DECLARE @VarDecimalSupported AS BIT;
        SELECT @VarDecimalSupported = 0;
        IF ((ServerProperty(N'EngineEdition') = 3)
            AND (((@@microsoftversion / power(2, 24) = 9)
                  AND (@@microsoftversion & 0xffff >= 3024))
                 OR ((@@microsoftversion / power(2, 24) = 10)
                     AND (@@microsoftversion & 0xffff >= 1600))))
            SELECT @VarDecimalSupported = 1;
        IF (@VarDecimalSupported > 0)
            BEGIN
                EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
            END
    END


GO
